<?php
function convertDateToUS($date){
		$dateData = explode(' ',$date);
		$date = explode('-',$dateData[0]);
		return $date[1].'/'.$date[2].'/'.$date[0];
	}
class CustomerAccountingController extends Controller {
 public function actionlistqbs($custno){
        $customer = Customers::model()->findByPk($custno);
      
          $data = Yii::app()->dbqb->createCommand("SELECT * FROM cc_quickbooks.receivepayment  
			where CustomerRef_FullName like '$customer->quickbook%' 
			and TimeCreated like '%2012%';")->queryAll();
           if (count($data) > 0) {
            $rs['totalCount'] =count($data) ;
            $rs['type'] = 'QB payments';
            foreach ($data as $i => $item) {
                $rs['data'][$i] = $item;
                $rs['data'][$i]['totalAmount'] = 0;
		$rs['data'][$i]['unusedPayment'] = 0;
		$rs['data'][$i]['invoiceAmount']=0;
		 $rs['data'][$i]['unusedPayment']=0;
		  $rs['data'][$i]['invoiceAmount']= floatval($item['TotalAmount']);
                    if($rs['data'][$i]['RefNumber']=='') 
            $rs['data'][$i]['unusedPayment']= $item['TotalAmount'];  
            	 $rs['data'][$i]['memo']=$item['Memo'];
            	 $rs['data'][$i]['TxnDate']=convertDateToUS($item['TxnDate']);
           $jobData = $item['CustomerRef_FullName'];
           $jobData = explode(':',$jobData);
           if(isset($jobData[1])) $jobNo = $jobData[1];
           else $jobNo = '';
            $rs['data'][$i]['CustomerRef_FullName'] = $jobNo;
                     
               
            }
            echo json_encode($rs);
        }
        Yii::app()->end();
        
    }
    public function actionlistqb($custno, $query = NULL, $page = 1, $sort = NULL, $dir = NULL) {
        $customer = Customers::model()->findByPk($custno);
        $criteria = new CDbCriteria();
        $criteria->condition = "customerRefFullName ='$customer->quickbook'";
        $criteria->order = "date, paymentTxnID ASC";
        $criteria->group = 'paymentTxnID';
        if ($page > 0)
            $page--;
        $data = new CActiveDataProvider('QbPaymentsapplied', array(
                    'criteria' => $criteria,
                    'pagination' => array(
                        'pageSize' => 99999999,
                        'currentPage' => $page,
                    )
                ));
        $rs = array();
        if (count($data->getData()) > 0) {
            $rs['totalCount'] = $data->getTotalItemCount();
            $rs['type'] = 'QB payments';
            foreach ($data->getData() as $i => $item) {
                $rs['data'][$i] = $item->getAttributes();
                $rs['data'][$i]['totalAmount'] = 0;
				$rs['data'][$i]['unusedPayment'] = 0;
                $models = QbPaymentsapplied::model()->findAll("paymentTxnID ='$item->paymentTxnID'");
                foreach ($models as $model) { 
                    $rs['data'][$i]['invoiceAmount']+= floatval($item->totalAmount);
                    if($rs['data'][$i]['invoiceNo']=='') $rs['data'][$i]['unusedPayment']+= floatval($item->totalAmount);  
                    }
                     
               
            }
            echo json_encode($rs);
        }
        Yii::app()->end();
    }
     public function actionlistso ($custno, $locno =null, $query = NULL, $page = 1, $sort = NULL, $dir = NULL) {
        $criteria = new CDbCriteria();
        $criteria->compare('custno',$custno);
        if(trim($locno)!=''){
            $criteria->compare('locno',$locno);    
        }
        
        $criteria->compare('invoicetype',Invoice::TYPE_SALES_ORDERS);
        
        $criteria->order = "invoicedate ASC";

        if ($page > 0)
            $page--;
        $data = new CActiveDataProvider('Invoice', array(
                    'criteria' => $criteria,
                    'pagination' => array(
                        'pageSize' => 99999999,
                        'currentPage' => $page,
                    )
                ));
        $rs = array();
        if (count($data->getData()) > 0) {
            $rs['totalCount'] = $data->getTotalItemCount();
            $rs['type'] = 'Invoices';
           
            foreach ($data->getData() as $i => $item) {
                $rs['data'][$i] = $item->getAttributes();
             //   $rs['data'][$i]['amount'] = $item->getPaymentApplied();
                $rs['data'][$i]['amount'] = $item->amountcharge;
                $datetime1 = new DateTime($item->duedate);
                $datetime2 = new DateTime();
                $interval = $datetime1->diff($datetime2);
                $rs['data'][$i]['balance'] = $item->amountcharge - $item->getPaymentApplied();
                if($rs['data'][$i]['balance']<0) $rs['data'][$i]['balance'] =0;
                $rs['data'][$i]['aging'] = $interval->days;
                $refacc = $item->customer->quickbook . ':' . $item->jobnumber;
                $criteria = new CDbCriteria();
                $criteria->condition = "customerRefFullName='$refacc'";
                $criteria->order = "date Asc";
                $paymentsApplied = QbPaymentsapplied::model()->findAll($criteria);
                $paymentsString = '';
                foreach ($paymentsApplied as $payment){
                    $paymentsString.= "$payment->totalAmount - $payment->memo <br/>";
                }
                $rs['data'][$i]['deposits'] = $paymentsString;  
            }
            echo json_encode($rs);
        }
        Yii::app()->end();
    }

    public function actionPayments($ref, $locno =null, $query = NULL, $page = 1, $sort = NULL, $dir = NULL) {
      $idkey = $ref;
         $data = Yii::app()->dbqb->createCommand("SELECT * FROM cc_quickbooks.appliedtotxndetail 
											where IDKEY='$idkey';")->queryAll(); 
    
        $rs = array();
        if (count($data) > 0) {
            $rs['totalCount'] = count($data);
            $rs['type'] = 'QB payments';
            foreach ($data as $i=>$item) {
                $rs['data'][$i] = $item;
                $rs['data'][$i]['invNo'] = $item['RefNumber'];
                
                $rs['data'][$i]['totalAmount'] = $item['Amount'];
                $invoice = Invoice::model()->findByPk($item['RefNumber']);
                if (isset($invoice)) {
                    $rs['data'][$i]['orgAmount'] =$invoice->amountcharge;
                    $rs['data'][$i]['jobnumber'] =$invoice->jobnumber;
                    
                    }
                else
                $rs['data'][$i]['orgAmount'] = 0;
            }
            echo json_encode($rs);
        }
        Yii::app()->end();
    }
       public function actionlistcm ($custno, $locno =null, $query = NULL, $page = 1, $sort = NULL, $dir = NULL) {
        $criteria = new CDbCriteria();
        $criteria->compare('custno',$custno);
        if(trim($locno)!=''){
            $criteria->compare('locno',$locno);    
        }
        $criteria->compare('invoicetype',Invoice::TYPE_CREDIT_MEMO);
        
        $criteria->order = "invoicedate ASC";

        if ($page > 0)
            $page--;
        $data = new CActiveDataProvider('Invoice', array(
                    'criteria' => $criteria,
                    'pagination' => array(
                        'pageSize' => 99999999,
                        'currentPage' => $page,
                    )
                ));
        $rs = array();
        if (count($data->getData()) > 0) {
            $rs['totalCount'] = $data->getTotalItemCount();
            $rs['type'] = 'Invoices';
           
            foreach ($data->getData() as $i => $item) {
                $rs['data'][$i] = $item->getAttributes();
             //   $rs['data'][$i]['amount'] = $item->getPaymentApplied();
                $rs['data'][$i]['amount'] = $item->amountcharge;
                $datetime1 = new DateTime($item->duedate);
                $datetime2 = new DateTime();
                $interval = $datetime1->diff($datetime2);
                $rs['data'][$i]['balance'] = $item->amountcharge;
                if($rs['data'][$i]['balance']<0) $rs['data'][$i]['balance'] =0;
                $rs['data'][$i]['aging'] = $interval->days;
            }
            echo json_encode($rs);
        }
        Yii::app()->end();
    }
 public function actionlistinv ($custno, $locno =null, $query = NULL, $page = 1, $sort = NULL, $dir = NULL) {
        $criteria = new CDbCriteria();
        $criteria->compare('custno',$custno);
        if(trim($locno)!=''){
            $criteria->compare('locno',$locno);    
        }
        $criteria->compare('invoicetype',Invoice::TYPE_INVOICE);

        $criteria->order = "invoicedate ASC";

        if ($page > 0)
            $page--;
        $data = new CActiveDataProvider('Invoice', array(
                    'criteria' => $criteria,
                    'pagination' => array(
                        'pageSize' => 99999999,
                        'currentPage' => $page,
                    )
                ));
        $rs = array();
        if (count($data->getData()) > 0) {
            $rs['totalCount'] = $data->getTotalItemCount();
            $rs['type'] = 'Invoices';
           
            foreach ($data->getData() as $i => $item) {
                $rs['data'][$i] = $item->getAttributes();
             //   $rs['data'][$i]['amount'] = $item->getPaymentApplied();
                $rs['data'][$i]['amount'] = $item->amountcharge;
                $datetime1 = new DateTime($item->duedate);
                $datetime2 = new DateTime();
                $interval = $datetime1->diff($datetime2);
                $rs['data'][$i]['balance'] = $item->amountcharge + $item->getPaymentApplied();
                if($rs['data'][$i]['balance']<0) $rs['data'][$i]['balance'] =0;
                $rs['data'][$i]['aging'] = $interval->days;
            }
            echo json_encode($rs);
        }
        Yii::app()->end();
    }


}
